/*
{*******************************************************************}
{                                                                   }
{       KS-GANTT DotNet Library                                     }
{       Gantt-Charting Control for Windows Forms                    }
{                                                                   }
{       Copyright (c) 2009 - 2012 by Kroll-Software                 }
{       All Rights Reserved                                         }
{                                                                   }
{   This code is provided 'as is',                                  }
{   without warranties of any kind. Use at your own risk.           }                                                              
{                                                                   }
{   As a license holder of the KS-Gantt Control,                    }
{   you may freely redistribute executables based on                }
{   and created with these contents.                                }
{   You can modify the source code to meet your needs.              }
{                                                                   }
{   However, you must not publish, redistribute or transfer         }
{   this source code to 3rd parties under any conditions.           }
{                                                                   } 
{*******************************************************************}
*/

using System.Diagnostics;
using System;
using System.Data;
using System.Collections;
using KS.Common;

namespace MiGuava.SqlServer
{
    public class SqlDatabase : IDisposable
    {
        public System.Data.SqlClient.SqlConnection DBConn;
        public System.Data.SqlClient.SqlTransaction Transaction = null;        

        public string DBServer;
        public string DBCatalog;
        public string DBUser;
        public string DBPassword;
        public bool DBWinSecurity;                

        private bool m_IsSysAdmin = false;
        public bool IsSysAdmin
        {
            get
            {
                return m_IsSysAdmin;
            }
        }
        
        public int ConnectionTimeout;
        public int CommandTimeout;
        
        public string CurrentUser
        {
            get
            {
                string returnValue;
                if (DBWinSecurity)
                {
                    returnValue = DatabaseHelpers.WindowsUser();
                }
                else
                {
                    returnValue = DBUser;
                }
                return returnValue;
            }
        }

        public string ConnectionString
        {
            get
            {
                return DatabaseHelpers.GetSQLServerConnectionString(DBServer, DBCatalog, DBWinSecurity, DBUser, DBPassword, ConnectionTimeout);
            }
        }

        public virtual void BeginTransaction()
        {
            if (Transaction != null)
            {
                throw new Exception("Transaction is not NULL");                
            }

            if (DBConn == null)
            {
                throw new Exception("DBConn is NULL");
            }

            this.Transaction = DBConn.BeginTransaction();            
        }

        public virtual void RollbackTransaction()
        {
            if (this.Transaction != null)
            {
                try
                {
                    this.Transaction.Rollback();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (Transaction != null)
                    {
                        Transaction.Dispose();
                        Transaction = null;
                    }
                }                
            }
        }

        public virtual void CommitTransaction()
        {
            if (this.Transaction != null)
            {
                try
                {
                    this.Transaction.Commit();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (Transaction != null)
                    {
                        Transaction.Dispose();
                        Transaction = null;
                    }
                }                                
            }
        }

        public SqlDatabase()
        {
            DBConn = new System.Data.SqlClient.SqlConnection();
            Reset();
        }

        ~SqlDatabase()
        {
            // Finalizer calls Dispose(false)
            Dispose(false);
        }

        public void Reset()
        {
            DBServer = "";
            DBCatalog = "";
            DBWinSecurity = true;
            DBUser = "";
            DBPassword = "";

            ConnectionTimeout = 20;
            CommandTimeout = 40;
        }


        public virtual bool Connect()
        {
            try
            {
                if (DBConn == null)
                    DBConn = new System.Data.SqlClient.SqlConnection();

                if (DBConn.State != ConnectionState.Closed)
                {
                    try
                    {
                        DBConn.Close();
                    }
                    catch (Exception)
                    {
                    }                    
                }

                DBConn.ConnectionString = ConnectionString;

                DBConn.Open();


                // Check if we are SysAdmin or DbCreator
                int iFlag = 0;
                string strSQL = "IF (IS_SRVROLEMEMBER ('sysadmin') = 1 ) BEGIN SELECT 1 END";
                strSQL += " ELSE IF (IS_SRVROLEMEMBER ('dbcreator') = 1 ) BEGIN SELECT 1 END";                    

                try
                {                    
                    iFlag = Strings.GetSafeInteger(ExecuteScalar(strSQL), 0);
                }
                catch (Exception)
                {
                }                

                m_IsSysAdmin = Strings.GetSafeBool(iFlag);

                return true;
            }
            catch (Exception ex)
            {
                m_IsSysAdmin = false;
                throw ex;                
            }
        }        

        public virtual void Disconnect()
        {
            if (DBConn == null)
                return;

            if (DBConn.State != ConnectionState.Closed)
            {
                try
                {
                    DBConn.Close();                    
                }
                catch (Exception)
                {                    
                }                
            }

            m_IsSysAdmin = false;
        }

        public object ExecuteScalar(string strSQL)
        {
            object retVal = null;
            System.Data.SqlClient.SqlCommand cmd = null;

            try
            {
                cmd = new System.Data.SqlClient.SqlCommand(strSQL, DBConn);
                if (Transaction != null)
                    cmd.Transaction = Transaction;

                cmd.CommandTimeout = this.CommandTimeout;
                retVal = cmd.ExecuteScalar();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
                        
            return retVal;
        }

        public System.Data.SqlClient.SqlDataReader ExecuteSQL(string strSQL)
        {
            System.Data.SqlClient.SqlCommand cmd = null;
            System.Data.SqlClient.SqlDataReader dr = null;

            try
            {
                cmd = new System.Data.SqlClient.SqlCommand(strSQL, DBConn);
                if (Transaction != null)
                    cmd.Transaction = Transaction;

                cmd.CommandTimeout = this.CommandTimeout;
                dr = cmd.ExecuteReader();
            }
            catch (Exception)
            {
                if (dr != null)
                {
                    dr.Dispose();
                    dr = null;
                }
                
                throw;
            }
            finally
            {                
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
                        
            return dr;
        }

        public long ExecuteUpdateSQL(string strSQL)
        {
            long returnValue = 0;

            System.Data.SqlClient.SqlCommand cmd = null;

            try
            {
                cmd = new System.Data.SqlClient.SqlCommand(strSQL, DBConn);
                if (Transaction != null)
                    cmd.Transaction = Transaction;

                cmd.CommandTimeout = CommandTimeout;                
                returnValue = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {                
                throw;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
                        
            return returnValue;
        }

        public int LastIdentity(string TableName)
        {
            System.Data.SqlClient.SqlCommand cmd = null;

            try
            {
                cmd = new System.Data.SqlClient.SqlCommand("select @@IDENTITY as ID from " + TableName, DBConn);
                if (Transaction != null)
                    cmd.Transaction = Transaction;

                return Strings.GetSafeInteger(cmd.ExecuteScalar(), 0);                
            }
            catch (Exception ex)
            {
                throw new Exception("LastIdentity() Error:\r" + ex.Message);
                //modGlobal.ErrMsgBox("LastIdentity() Error:\r" + ex.Message);
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public void LoadSettings()
        {
            Reset();

            KSRegistry cReg = new KSRegistry();
            cReg.sectionKey = Program.C_RegistrySectionKey;

            cReg.valueKey = "DBServer";
            cReg.defaultValue = "";
            DBServer = cReg.value.ToString();

            cReg.valueKey = "DBCatalog";
            cReg.defaultValue = "";
            DBCatalog = cReg.value.ToString();

            cReg.valueKey = "DBIntegratedSecurity";
            cReg.defaultValue = true;
            DBWinSecurity = System.Convert.ToBoolean(cReg.value);

            cReg.valueKey = "DBUser";
            cReg.defaultValue = "";
            DBUser = cReg.value.ToString();

            cReg.valueKey = "DBConnectionTimeout";
            cReg.defaultValue = 20;
            ConnectionTimeout = Strings.GetSafeInteger(cReg.value, 20);

            cReg.valueKey = "DBCommandTimeout";
            cReg.defaultValue = 40;
            CommandTimeout = Strings.GetSafeInteger(cReg.value, 40);            
        }

        public void SaveSettings()
        {            
            KSRegistry cReg = new KSRegistry();
            cReg.sectionKey = Program.C_RegistrySectionKey;

            cReg.valueKey = "DBServer";
            cReg.defaultValue = "";
            cReg.value = DBServer;

            cReg.valueKey = "DBCatalog";
            cReg.defaultValue = "";
            cReg.value = DBCatalog;

            cReg.valueKey = "DBIntegratedSecurity";
            cReg.defaultValue = false;
            cReg.value = DBWinSecurity;

            cReg.valueKey = "DBUser";
            cReg.defaultValue = "";
            cReg.value = DBUser;

            cReg.valueKey = "DBConnectionTimeout";
            cReg.defaultValue = 20;
            cReg.value = ConnectionTimeout;

            cReg.valueKey = "DBCommandTimeout";
            cReg.defaultValue = 40;
            cReg.value = CommandTimeout;            
        }


        private bool UseDefaultDB()
        {
            try
            {
                this.ExecuteUpdateSQL("USE " + DBCatalog);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool CreateDatabaseUser(string UserName, string Password)
        {
            try
            {
                this.ExecuteUpdateSQL("USE MASTER");
                this.ExecuteUpdateSQL("EXEC sp_addlogin '" + UserName + "', '" + Password + "'");
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                switch (ex.Number)
                {
                    case 15025:     // Benutzer existiert bereits
                        break;

                    default:
                        throw ex;                        
                }
            }
            finally
            {
                UseDefaultDB();
            }

            try
            {
                this.ExecuteUpdateSQL("USE " + DBCatalog);
                this.ExecuteUpdateSQL("EXEC sp_grantdbaccess '" + UserName + "'");
                this.ExecuteUpdateSQL("EXEC sp_addrolemember 'db_owner', '" + UserName + "'");
                // Stattdessen als SysAdmin anlegen: exec sp_addsrvrolemember 'LoginName', 'ServerRolle'

                return true;
            }
            catch (Exception ex)
            {
                throw ex;                
            }
            finally
            {
                UseDefaultDB();
            }
        }

        public bool DeleteDatabaseUser(string UserName)
        {
            if (UserName == "")
            {
                throw new Exception("Empty user specified.");
            }

            if (UserName.ToLower() == "sa")
            {
                throw new Exception("User 'sa' cannot be deleted.");
            }            

            try
            {
                this.ExecuteUpdateSQL("EXEC sp_dropuser '" + UserName + "'");
                this.ExecuteUpdateSQL("USE MASTER");
                this.ExecuteUpdateSQL("EXEC sp_droplogin '" + UserName + "'");
                this.ExecuteUpdateSQL("USE " + DBCatalog);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;                
            }
            finally
            {
                UseDefaultDB();
            }
        }

        public bool ChangeDatabasePassword(string UserName, string OldPassword, string NewPassword)
        {
            try
            {
                this.ExecuteUpdateSQL("USE MASTER");
                this.ExecuteUpdateSQL("EXEC sp_password '" + OldPassword + "', '" + NewPassword + "', '" + UserName + "'");
                this.ExecuteUpdateSQL("USE " + DBCatalog);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;                
            }
            finally
            {
                UseDefaultDB();
            }
        }

        public bool UserExists()
        {
            bool bUserExists = false;

            System.Data.SqlClient.SqlDataReader dr;
            dr = this.ExecuteSQL("select ID from Users where LogIn='" + this.CurrentUser + "'");

            if (dr != null && dr.Read())
            {
                bUserExists = true;
            }

            if (dr != null)
            {
                dr.Close();
                dr.Dispose();
            }

            return bUserExists;
        }

        public int CreateUserRecord()
        {
            SQLBuilder SQL = new SQLBuilder("Users", DBMSTypes.MSSQLServer);

            SQL.AddColumn("LogIn", SQLBuilderTypes.sqlString, false, 50);
            SQL.AddValue("LogIn", this.CurrentUser);

            SQL.AddColumn("Password", SQLBuilderTypes.sqlString, false, 50);
            SQL.AddValue("Password", this.DBPassword);

            int RetValue = 0;

            try
            {
                this.ExecuteUpdateSQL(SQL.InsertCommand());
                RetValue = this.LastIdentity("Users");
                return RetValue;
            }
            catch (Exception ex)
            {
                throw ex;                
            }
        }

        public bool DeleteUserRecord(string strLogIn)
        {
            if (strLogIn == "")
            {
                throw new Exception("DeleteUserRecord: Empty user specified");                
            }

            string strSQL = "delete from Users where LogIn='" + strLogIn + "'";

            try
            {
                this.ExecuteUpdateSQL(strSQL);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;                
            }
        }

        public bool UpdatePassword()
        {
            int ID = 0;

            System.Data.SqlClient.SqlDataReader dr = null;

            try
            {
                dr = this.ExecuteSQL("select ID, Password from Users where LogIn='" + this.CurrentUser + "'");
                if (dr.Read())
                {
                    if (Strings.GetSafeString(dr["Password"]) != this.DBPassword)
                        ID = Strings.GetSafeInteger(dr["ID"], 0);
                }
            }
            catch (Exception ex)
            {
                throw ex;                
            }

            if (dr != null)
            {
                dr.Close();
                dr.Dispose();
            }

            if (ID > 0)
            {
                try
                {
                    this.ExecuteUpdateSQL("update Users set Password=" + Strings.ConvertNullSQL(this.DBPassword) + " WHERE ID=" + ID);
                }
                catch (Exception ex)
                {
                    throw ex;                
                }
            }

            return true;
        }

        public bool IsConnected
        {
            get
            {
                return (DBConn != null && DBConn.State != 0);
            }            
        }

        // IDisposable        
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }        
    
        // The bulk of the clean-up code is implemented in Dispose(bool)
        protected virtual void Dispose(bool disposing)
        {
            if (disposing) 
            {
                // free managed resources            
                if (DBConn != null)
                {
                    DBConn.Dispose();
                    DBConn = null;
                }

                if (Transaction != null)
                {
                    Transaction.Dispose();
                    Transaction = null;
                }
            }            
        }

    }

}
